﻿
Partial Class WebForm_ManageTCUploadExcel_ManageYTDACTUploadExcel
    Inherits System.Web.UI.UserControl

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            pl.DataBind()
        End If
    End Sub

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        Dim sql As New StringBuilder

        If Not FileUpload1.HasFile Then
            MyObj.ShowMessage("no file")
            Return
        ElseIf IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper() <> ".XLS" AndAlso IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper() <> ".XLSX" Then
            MyObj.ShowMessage("Import failed!!Please select the file extension (xls/xlsx) file!!")
            Return
        End If

        '上傳暫存檔
        Dim clsRandom As New clsRandom()
        Dim newName As String = Server.MapPath("~/") & "/tmp/" & clsRandom.Generate()
        FileUpload1.PostedFile.SaveAs(newName)

        Dim ExcelImportCheck As New ExcelImportCheck()
        Dim dt As New Data.DataTable

        '檢查工作表名稱
        Try
            If ExcelImportCheck.LoadData(dt, newName, "upload", IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper()) = False Then
                Return
            End If
        Catch ex As Exception
            MyObj.ShowMessage("Please check the file format!!!")
            Exit Sub
        End Try

        '新增Error欄位
        dt.Columns.Add("Error", GetType(String))

        '去除空行
        Dim dv As New DataView(dt)

        dv.RowFilter = "Signature IS NOT NULL"
        dt = dv.ToTable

        '檢查資料
        Dim errorList As New List(Of String)

        For Each dr As DataRow In dt.Rows
            Dim errors As New StringBuilder
            Dim result() As DataRow

            '檢查 ItemName
            If MyObj.CheckYTDItemName(dr("Account Profit And Loss").ToString().Trim()) = False Then
                errors.AppendFormat("Invalid ItemName ")
            End If

            '不可為非數字　
            'If IsNumeric(dr("Target").ToString()) = False Then
            '    errors.AppendFormat("Value should be numeric. ")
            'End If

            '檢查 Signature
            result = SignatureData.Select(String.Format("Signature='{0}'", dr("Signature")))
            If result.Length = 0 Then
                errors.AppendFormat("Signature is invalid ")
            Else
                dr("Signature") = result(0)("Signature")
            End If

            '檢查 Channel
            result = ChannelData.Select(String.Format("Channel='{0}'", dr("Customer Group")))
            If result.Length = 0 Then
                errors.AppendFormat("Customer Group is invalid ")
            Else
                dr("Customer Group") = result(0)("Channel")
            End If

            '檢查 Hier1
            result = Hier1Data.Select(String.Format("c_hier_1='{0}'", dr("Customer Hierarchy1")))
            If result.Length = 0 Then
                errors.AppendFormat("Customer Hierarchy1 is invalid ")
            Else
                'dr("Customer Hierarchy1") = result(0)("c_hier_1").ToString
            End If

            If errors.Length > 0 Then
                dr("Error") = errors.ToString
                errorList.Add(errors.ToString)
            End If
        Next

        For Each dc As DataColumn In dt.Columns
            Dim d As DateTime

            '檢查月份欄位 yyyy/MM
            If Date.TryParse(dc.ColumnName + "/01", d) Then
                If d.Year = Integer.Parse(ddlYear.Text) Then
                    dc.ColumnName = String.Format("Mon{0}", d.Month)
                Else
                    Dim ee As String = "Year dismatch "
                    errorList.Add(ee)
                    MyObj.ShowMessage(ee)
                End If
            End If
        Next

        If errorList.Count > 0 Then
            MyObj.ShowMessage("Upload Failed.Data Error Count:" & errorList.Count)
        Else
            '寫入資料
            dt.Columns.Add("YYMM").Expression = ddlYear.Text
            'dt.Columns.Add("Value", GetType(Single)).Expression = "Convert(Target, 'System.Double')"

            dt.Columns.Add("Customer Hierarchy1 string", GetType(String))

            '取得月欄位
            Dim mons As New List(Of Integer)

            For Each dc As DataColumn In dt.Columns                
                If dc.ColumnName.Contains("Mon") And dc.ColumnName.Length <= 5 Then
                    mons.Add(Integer.Parse(dc.ColumnName.Substring(3)))
                End If
            Next

            For Each dr As DataRow In dt.Rows

                '避免NULL值
                If dr("Customer Group") Is DBNull.Value Then
                    dr("Customer Group") = String.Empty
                End If

                dr("Customer Hierarchy1 string") = dr("Customer Hierarchy1")

                '避免NULL值
                If dr("Customer Hierarchy1 string") Is DBNull.Value Then
                    dr("Customer Hierarchy1 string") = String.Empty
                End If
            Next

            Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings("connStr").ConnectionString)
                cn.Open()

                Dim dat As New clsData(cn)
                sql.Length = 0
                sql.AppendFormat("SELECT TOP 0 * INTO #TCReportAHier1Data FROM TCReportAHier1Data ")
                dat.AccData(sql.ToString)

                Using copy As New SqlBulkCopy(cn)
                    copy.ColumnMappings.Add("YYMM", "YYMM")
                    copy.ColumnMappings.Add("Account Profit And Loss", "ItemName")
                    copy.ColumnMappings.Add("Signature", "Signature")
                    copy.ColumnMappings.Add("Customer Group", "Channel")
                    copy.ColumnMappings.Add("Customer Hierarchy1 string", "Hier1")
                    'copy.ColumnMappings.Add("Target", "YTDValue")

                    For Each mm As Integer In mons
                        copy.ColumnMappings.Add(String.Format("Mon{0}", mm), String.Format("Mon{0}", mm))
                    Next

                    copy.DestinationTableName = "#TCReportAHier1Data"
                    copy.WriteToServer(dt)
                End Using

                'sql.Length = 0
                'sql.AppendFormat("INSERT TCReportAHier1Data ")
                'sql.AppendFormat("SELECT *  FROM #TCReportAHier1Data ytd ")

                '新增不存在的 Item
                sql.Length = 0
                sql.AppendFormat("INSERT TCReportAHier1Data (YYMM,Signature,Channel,Hier1,ItemName) ")
                sql.AppendFormat("SELECT a.YYMM,a.Signature,a.Channel,a.Hier1,a.ItemName FROM #TCReportAHier1Data a ")
                sql.AppendFormat("LEFT JOIN TCReportAHier1Data t ON a.YYMM = t.YYMM AND a.Signature = t.Signature AND a.Channel = t.Channel AND a.Hier1 = t.Hier1 ")
                sql.AppendFormat("  AND a.ItemName = t.ItemName ")
                sql.AppendFormat("WHERE t.ItemName IS NULL ")

                dat.AccData(sql.ToString)

                sql.Length = 0
                sql.AppendFormat("UPDATE a SET " + MyObj.JoinFormat(",", "Mon{0} = t.Mon{0} ", mons))
                sql.AppendFormat("FROM TCReportAHier1Data a ")
                sql.AppendFormat("INNER JOIN #TCReportAHier1Data t ON a.YYMM = t.YYMM AND a.Signature = t.Signature AND a.Channel = t.Channel AND a.Hier1 = t.Hier1 ")
                sql.AppendFormat("  AND a.ItemName = t.ItemName ")

                dat.AccData(sql.ToString)

                sql.Length = 0
                sql.AppendFormat("SELECT * FROM #TCReportAHier1Data ")
            End Using
        End If

        dt.DefaultView.RowFilter = "Error<>''"
        TCGVDT = dt.DefaultView.ToTable

        Me.BindGV()
    End Sub

    Protected Sub gv_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gv.PageIndexChanging
        Me.gv.PageIndex = e.NewPageIndex
        Me.BindGV()
    End Sub

    Dim TCGVDT As DataTable

    Protected Sub BindGV()
        gv.DataSource = TCGVDT
        gv.DataBind()

        gv.Visible = gv.Rows.Count > 0
    End Sub

    Protected ReadOnly Property SignatureData() As DataTable
        Get
            If HttpContext.Current.Items("SignatureData") Is Nothing Then
                Dim dv As New DataView(Hier1Data)

                HttpContext.Current.Items("SignatureData") = dv.ToTable(True, "Signature", "SignatureName")
            End If

            Return TryCast(HttpContext.Current.Items("SignatureData"), DataTable)
        End Get
    End Property

    Protected ReadOnly Property ChannelData() As DataTable
        Get
            If HttpContext.Current.Items("ChannelData") Is Nothing Then
                Dim dv As New DataView(Hier1Data)

                HttpContext.Current.Items("ChannelData") = dv.ToTable(True, "Channel", "ChannelName")
            End If

            Return TryCast(HttpContext.Current.Items("ChannelData"), DataTable)
        End Get
    End Property

    Protected ReadOnly Property Hier1Data() As DataTable
        Get
            If HttpContext.Current.Items("Hier1Data") Is Nothing Then
                Dim dat As New clsData
                Dim paras As New List(Of SqlParameter)
                Dim sql As New StringBuilder

                sql.AppendFormat("SELECT h.*, s.Description AS SignatureName FROM ChannelHier1View h ")
                sql.AppendFormat("INNER JOIN ProductHierarchyOriginal s ON h.Signature = s.code AND s.LevelName = 'Signature' ")
                sql.AppendFormat("WHERE Division = @Division ")

                paras.Add(New SqlParameter("Division", ddlDivision.Text))

                HttpContext.Current.Items("Hier1Data") = dat.GetData(sql.ToString, paras.ToArray)
            End If

            Return TryCast(HttpContext.Current.Items("Hier1Data"), DataTable)
        End Get
    End Property

    Protected ReadOnly Property YTDData() As DataTable
        Get
            If HttpContext.Current.Items("YTDData") Is Nothing Then
                Dim dat As New clsData
                Dim paras As New List(Of SqlParameter)
                Dim sql As New StringBuilder

                sql.Length = 0
                sql.AppendFormat("SELECT * FROM TCReportAHier1Data ytd ")
                sql.AppendFormat("WHERE YYMM = @YYMM AND Signature IN (SELECT DISTINCT Signature FROM ChannelView WHERE Division = @Division) ")

                paras.Add(New SqlParameter("YYMM", ddlYear.Text))
                paras.Add(New SqlParameter("Division", ddlDivision.Text))

                HttpContext.Current.Items("YTDData") = dat.GetData(sql.ToString, paras.ToArray)
            End If

            Return TryCast(HttpContext.Current.Items("YTDData"), DataTable)
        End Get
    End Property

End Class
